


--use PolePosition

-- // checksum failed custom error
--exec sp_addmessage @msgnum = 505050, @severity = 16, @msgtext = N'%s(%s) checksum compare failed', @replace = 'REPLACE', @lang = 'us_english'
GO

-- // not found custom error
--exec sp_addmessage @msgnum = 505060, @severity = 16, @msgtext = N'%s(%s) does not exist', @replace = 'REPLACE', @lang = 'us_english'
GO

-- // unit test custom error
--exec sp_addmessage @msgnum = 505070, @severity = 16, @msgtext = N'%s Test Failed. Expected< %s > Actual< %s > %s', @replace = 'REPLACE', @lang = 'us_english'
GO



--use PolePosition

if exists (select * from dbo.sysobjects where id = object_id(N'v_tb_Utility_Asset_XMissionStructure_Poles_CrossRemedy_Vendor_InspectionVisit') and OBJECTPROPERTY(id, N'IsProcedure') = 0)
drop view v_tb_Utility_Asset_XMissionStructure_Poles_CrossRemedy_Vendor_InspectionVisit
GO

create view v_tb_Utility_Asset_XMissionStructure_Poles_CrossRemedy_Vendor_InspectionVisit as
	/* 	Initial Generated Date:  Saturday, February 07, 2009 
		Purpose: Selected record(s) from the table; this view is the source for all procedures' select statements
	*/
	
select
	 [Utility_Asset_XMissionStructure_Pole_CrossRemedy_Vendor_InspectionVisitId] as [Utility_Asset_XMissionStructure_Pole_CrossRemedy_Vendor_InspectionVisitId]
	,[Vendor_InspVisit_XMissionStructure_PoleFK] as [Vendor_InspVisit_XMissionStructure_PoleFK]
	,[Utility_Asset_XMissionStructureFK] as [Utility_Asset_XMissionStructureFK]
	,[DiscoveredDate] as [DiscoveredDate]
	,[RemedyStatusFK] as [RemedyStatusFK]
	,[RemedyAssigned] as [RemedyAssigned]
	,[RemedyLastAction] as [RemedyLastAction]
	,[RemedyResolvedDate] as [RemedyResolvedDate]
	,[RemedyNotes] as [RemedyNotes]
	-- customization may be required for FriendlyName
	,Left(CONVERT(varchar(255), [Utility_Asset_XMissionStructure_Pole_CrossRemedy_Vendor_InspectionVisitId]), 255) as [FriendlyName]
from
	tb_Utility_Asset_XMissionStructure_Poles_CrossRemedy_Vendor_InspectionVisit
GO





--use PolePosition

if exists (select * from dbo.sysobjects where id = object_id(N'up_UtilityAssetXMissionStructurePolesCrossRemedyVendorInspectionVisit_Test') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure up_UtilityAssetXMissionStructurePolesCrossRemedyVendorInspectionVisit_Test
GO


create proc up_UtilityAssetXMissionStructurePolesCrossRemedyVendorInspectionVisit_Test
	@Utility_Asset_XMissionStructure_Pole_CrossRemedy_Vendor_InspectionVisitId int
as
	/* 	Initial Generated Date:  Saturday, February 07, 2009 
		Purpose: Test if a record exists, using the primary key
	*/

SET NOCOUNT ON	
	
if exists(
	select [Utility_Asset_XMissionStructure_Pole_CrossRemedy_Vendor_InspectionVisitId] 
	from [tb_Utility_Asset_XMissionStructure_Poles_CrossRemedy_Vendor_InspectionVisit] source
	where [source].[Utility_Asset_XMissionStructure_Pole_CrossRemedy_Vendor_InspectionVisitId] = @Utility_Asset_XMissionStructure_Pole_CrossRemedy_Vendor_InspectionVisitId)
begin
	return(1)
end
else
begin
	return(0)
end
GO






--use PolePosition

if exists (select * from dbo.sysobjects where id = object_id(N'up_UtilityAssetXMissionStructurePolesCrossRemedyVendorInspectionVisit_Select') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure up_UtilityAssetXMissionStructurePolesCrossRemedyVendorInspectionVisit_Select
GO

create proc up_UtilityAssetXMissionStructurePolesCrossRemedyVendorInspectionVisit_Select
	 @Utility_Asset_XMissionStructure_Pole_CrossRemedy_Vendor_InspectionVisitId int = null 
	,@Vendor_InspVisit_XMissionStructure_PoleFK int = null 
	,@Utility_Asset_XMissionStructureFK int = null 
	,@DiscoveredDate datetime = null 
	,@RemedyStatusFK int = null 
	,@RemedyAssigned nvarchar(150) = null 
	,@RemedyLastAction datetime = null 
	,@RemedyResolvedDate datetime = null 
	,@RemedyNotes text = null 
as
	/* 	Initial Generated Date:  Saturday, February 07, 2009 
		Purpose: Selected record(s) based on the passed arguments; no arguments returns all
	*/

SET NOCOUNT ON	

select * from v_tb_Utility_Asset_XMissionStructure_Poles_CrossRemedy_Vendor_InspectionVisit
where
		IsNull([v_tb_Utility_Asset_XMissionStructure_Poles_CrossRemedy_Vendor_InspectionVisit].[Utility_Asset_XMissionStructure_Pole_CrossRemedy_Vendor_InspectionVisitId], -1) = Coalesce(@Utility_Asset_XMissionStructure_Pole_CrossRemedy_Vendor_InspectionVisitId, [v_tb_Utility_Asset_XMissionStructure_Poles_CrossRemedy_Vendor_InspectionVisit].[Utility_Asset_XMissionStructure_Pole_CrossRemedy_Vendor_InspectionVisitId], -1)
	AND	IsNull([v_tb_Utility_Asset_XMissionStructure_Poles_CrossRemedy_Vendor_InspectionVisit].[Vendor_InspVisit_XMissionStructure_PoleFK], -1) = Coalesce(@Vendor_InspVisit_XMissionStructure_PoleFK, [v_tb_Utility_Asset_XMissionStructure_Poles_CrossRemedy_Vendor_InspectionVisit].[Vendor_InspVisit_XMissionStructure_PoleFK], -1)
	AND	IsNull([v_tb_Utility_Asset_XMissionStructure_Poles_CrossRemedy_Vendor_InspectionVisit].[Utility_Asset_XMissionStructureFK], -1) = Coalesce(@Utility_Asset_XMissionStructureFK, [v_tb_Utility_Asset_XMissionStructure_Poles_CrossRemedy_Vendor_InspectionVisit].[Utility_Asset_XMissionStructureFK], -1)
	AND	IsNull([v_tb_Utility_Asset_XMissionStructure_Poles_CrossRemedy_Vendor_InspectionVisit].[DiscoveredDate], '1/1/1900 12:00AM') = Coalesce(@DiscoveredDate, [v_tb_Utility_Asset_XMissionStructure_Poles_CrossRemedy_Vendor_InspectionVisit].[DiscoveredDate], '1/1/1900 12:00AM')
	AND	IsNull([v_tb_Utility_Asset_XMissionStructure_Poles_CrossRemedy_Vendor_InspectionVisit].[RemedyStatusFK], -1) = Coalesce(@RemedyStatusFK, [v_tb_Utility_Asset_XMissionStructure_Poles_CrossRemedy_Vendor_InspectionVisit].[RemedyStatusFK], -1)
	AND	IsNull([v_tb_Utility_Asset_XMissionStructure_Poles_CrossRemedy_Vendor_InspectionVisit].[RemedyAssigned], '') = Coalesce(@RemedyAssigned, [v_tb_Utility_Asset_XMissionStructure_Poles_CrossRemedy_Vendor_InspectionVisit].[RemedyAssigned], '')
	AND	IsNull([v_tb_Utility_Asset_XMissionStructure_Poles_CrossRemedy_Vendor_InspectionVisit].[RemedyLastAction], '1/1/1900 12:00AM') = Coalesce(@RemedyLastAction, [v_tb_Utility_Asset_XMissionStructure_Poles_CrossRemedy_Vendor_InspectionVisit].[RemedyLastAction], '1/1/1900 12:00AM')
	AND	IsNull([v_tb_Utility_Asset_XMissionStructure_Poles_CrossRemedy_Vendor_InspectionVisit].[RemedyResolvedDate], '1/1/1900 12:00AM') = Coalesce(@RemedyResolvedDate, [v_tb_Utility_Asset_XMissionStructure_Poles_CrossRemedy_Vendor_InspectionVisit].[RemedyResolvedDate], '1/1/1900 12:00AM')
	AND	IsNull(Convert(varchar(2000), [v_tb_Utility_Asset_XMissionStructure_Poles_CrossRemedy_Vendor_InspectionVisit].[RemedyNotes]), '') = Coalesce(Convert(varchar(2000), @RemedyNotes), Convert(varchar(2000), [v_tb_Utility_Asset_XMissionStructure_Poles_CrossRemedy_Vendor_InspectionVisit].[RemedyNotes]), '')
GO





--use PolePosition

if exists (select * from dbo.sysobjects where id = object_id(N'up_UtilityAssetXMissionStructurePolesCrossRemedyVendorInspectionVisit_Select_Top1') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure up_UtilityAssetXMissionStructurePolesCrossRemedyVendorInspectionVisit_Select_Top1
GO

create proc up_UtilityAssetXMissionStructurePolesCrossRemedyVendorInspectionVisit_Select_Top1
	 @Utility_Asset_XMissionStructure_Pole_CrossRemedy_Vendor_InspectionVisitId int = null 
	,@Vendor_InspVisit_XMissionStructure_PoleFK int = null 
	,@Utility_Asset_XMissionStructureFK int = null 
	,@DiscoveredDate datetime = null 
	,@RemedyStatusFK int = null 
	,@RemedyAssigned nvarchar(150) = null 
	,@RemedyLastAction datetime = null 
	,@RemedyResolvedDate datetime = null 
	,@RemedyNotes text = null 
as
	/* 	Initial Generated Date:  Saturday, February 07, 2009 
		Purpose: Selected top 1 record based on the passed arguments; no arguments returns all
	*/

SET NOCOUNT ON	

select TOP 1 * from v_tb_Utility_Asset_XMissionStructure_Poles_CrossRemedy_Vendor_InspectionVisit
where
		IsNull([v_tb_Utility_Asset_XMissionStructure_Poles_CrossRemedy_Vendor_InspectionVisit].[Utility_Asset_XMissionStructure_Pole_CrossRemedy_Vendor_InspectionVisitId], -1) = Coalesce(@Utility_Asset_XMissionStructure_Pole_CrossRemedy_Vendor_InspectionVisitId, [v_tb_Utility_Asset_XMissionStructure_Poles_CrossRemedy_Vendor_InspectionVisit].[Utility_Asset_XMissionStructure_Pole_CrossRemedy_Vendor_InspectionVisitId], -1)
	AND	IsNull([v_tb_Utility_Asset_XMissionStructure_Poles_CrossRemedy_Vendor_InspectionVisit].[Vendor_InspVisit_XMissionStructure_PoleFK], -1) = Coalesce(@Vendor_InspVisit_XMissionStructure_PoleFK, [v_tb_Utility_Asset_XMissionStructure_Poles_CrossRemedy_Vendor_InspectionVisit].[Vendor_InspVisit_XMissionStructure_PoleFK], -1)
	AND	IsNull([v_tb_Utility_Asset_XMissionStructure_Poles_CrossRemedy_Vendor_InspectionVisit].[Utility_Asset_XMissionStructureFK], -1) = Coalesce(@Utility_Asset_XMissionStructureFK, [v_tb_Utility_Asset_XMissionStructure_Poles_CrossRemedy_Vendor_InspectionVisit].[Utility_Asset_XMissionStructureFK], -1)
	AND	IsNull([v_tb_Utility_Asset_XMissionStructure_Poles_CrossRemedy_Vendor_InspectionVisit].[DiscoveredDate], '1/1/1900 12:00AM') = Coalesce(@DiscoveredDate, [v_tb_Utility_Asset_XMissionStructure_Poles_CrossRemedy_Vendor_InspectionVisit].[DiscoveredDate], '1/1/1900 12:00AM')
	AND	IsNull([v_tb_Utility_Asset_XMissionStructure_Poles_CrossRemedy_Vendor_InspectionVisit].[RemedyStatusFK], -1) = Coalesce(@RemedyStatusFK, [v_tb_Utility_Asset_XMissionStructure_Poles_CrossRemedy_Vendor_InspectionVisit].[RemedyStatusFK], -1)
	AND	IsNull([v_tb_Utility_Asset_XMissionStructure_Poles_CrossRemedy_Vendor_InspectionVisit].[RemedyAssigned], '') = Coalesce(@RemedyAssigned, [v_tb_Utility_Asset_XMissionStructure_Poles_CrossRemedy_Vendor_InspectionVisit].[RemedyAssigned], '')
	AND	IsNull([v_tb_Utility_Asset_XMissionStructure_Poles_CrossRemedy_Vendor_InspectionVisit].[RemedyLastAction], '1/1/1900 12:00AM') = Coalesce(@RemedyLastAction, [v_tb_Utility_Asset_XMissionStructure_Poles_CrossRemedy_Vendor_InspectionVisit].[RemedyLastAction], '1/1/1900 12:00AM')
	AND	IsNull([v_tb_Utility_Asset_XMissionStructure_Poles_CrossRemedy_Vendor_InspectionVisit].[RemedyResolvedDate], '1/1/1900 12:00AM') = Coalesce(@RemedyResolvedDate, [v_tb_Utility_Asset_XMissionStructure_Poles_CrossRemedy_Vendor_InspectionVisit].[RemedyResolvedDate], '1/1/1900 12:00AM')
	AND	IsNull(Convert(varchar(2000), [v_tb_Utility_Asset_XMissionStructure_Poles_CrossRemedy_Vendor_InspectionVisit].[RemedyNotes]), '') = Coalesce(Convert(varchar(2000), @RemedyNotes), Convert(varchar(2000), [v_tb_Utility_Asset_XMissionStructure_Poles_CrossRemedy_Vendor_InspectionVisit].[RemedyNotes]), '')
GO




--use PolePosition

if exists (select * from dbo.sysobjects where id = object_id(N'up_UtilityAssetXMissionStructurePolesCrossRemedyVendorInspectionVisit_Insert') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure up_UtilityAssetXMissionStructurePolesCrossRemedyVendorInspectionVisit_Insert
GO

create proc up_UtilityAssetXMissionStructurePolesCrossRemedyVendorInspectionVisit_Insert
	 @Utility_Asset_XMissionStructure_Pole_CrossRemedy_Vendor_InspectionVisitId int =null OUTPUT  
	,@Vendor_InspVisit_XMissionStructure_PoleFK int = null  
	,@Utility_Asset_XMissionStructureFK int = null  
	,@DiscoveredDate datetime = null  
	,@RemedyStatusFK int = null  
	,@RemedyAssigned nvarchar(150) = null  
	,@RemedyLastAction datetime = null  
	,@RemedyResolvedDate datetime = null  
	,@RemedyNotes text = null  
as
	/* 	Initial Generated Date:  Saturday, February 07, 2009 
		Purpose: Inserts a new record; missing arguments are set as their default (typically null)
	*/

SET NOCOUNT ON	
--int

insert tb_Utility_Asset_XMissionStructure_Poles_CrossRemedy_Vendor_InspectionVisit 
	(
	
	 [Vendor_InspVisit_XMissionStructure_PoleFK] 
	,[Utility_Asset_XMissionStructureFK] 
	,[DiscoveredDate] 
	,[RemedyStatusFK] 
	,[RemedyAssigned] 
	,[RemedyLastAction] 
	,[RemedyResolvedDate] 
	,[RemedyNotes] 
	)
	values
	(
	
	 @Vendor_InspVisit_XMissionStructure_PoleFK 
	,@Utility_Asset_XMissionStructureFK 
	,@DiscoveredDate 
	,@RemedyStatusFK 
	,@RemedyAssigned 
	,@RemedyLastAction 
	,@RemedyResolvedDate 
	,@RemedyNotes 
	)
set @Utility_Asset_XMissionStructure_Pole_CrossRemedy_Vendor_InspectionVisitId = scope_identity()
select * from v_tb_Utility_Asset_XMissionStructure_Poles_CrossRemedy_Vendor_InspectionVisit where Utility_Asset_XMissionStructure_Pole_CrossRemedy_Vendor_InspectionVisitId = @Utility_Asset_XMissionStructure_Pole_CrossRemedy_Vendor_InspectionVisitId

GO




--use PolePosition

if exists (select * from dbo.sysobjects where id = object_id(N'up_UtilityAssetXMissionStructurePolesCrossRemedyVendorInspectionVisit_Update') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure up_UtilityAssetXMissionStructurePolesCrossRemedyVendorInspectionVisit_Update
GO


create proc up_UtilityAssetXMissionStructurePolesCrossRemedyVendorInspectionVisit_Update
	 @Utility_Asset_XMissionStructure_Pole_CrossRemedy_Vendor_InspectionVisitId int  
	,@Vendor_InspVisit_XMissionStructure_PoleFK int = null  
	,@Utility_Asset_XMissionStructureFK int = null  
	,@DiscoveredDate datetime = null  
	,@RemedyStatusFK int = null  
	,@RemedyAssigned nvarchar(150) = null  
	,@RemedyLastAction datetime = null  
	,@RemedyResolvedDate datetime = null  
	,@RemedyNotes text = null  
	,@OriginalUtility_Asset_XMissionStructure_Pole_CrossRemedy_Vendor_InspectionVisitId int  = null --Original PrimaryKey
	
as
	/* 	Initial Generated Date:  Saturday, February 07, 2009 
		Purpose: Updates an existing record, verifying exist; missing arguments persist original record value
		NOTE: this version of the update allows up to write nulls to the table.
	*/

SET NOCOUNT ON	

if @OriginalUtility_Asset_XMissionStructure_Pole_CrossRemedy_Vendor_InspectionVisitId is null
	set @OriginalUtility_Asset_XMissionStructure_Pole_CrossRemedy_Vendor_InspectionVisitId = @Utility_Asset_XMissionStructure_Pole_CrossRemedy_Vendor_InspectionVisitId

-- test
declare @Result bit
execute @Result = up_UtilityAssetXMissionStructurePolesCrossRemedyVendorInspectionVisit_Test @OriginalUtility_Asset_XMissionStructure_Pole_CrossRemedy_Vendor_InspectionVisitId
if (not @Result = 1)
begin
	Declare @x varchar(100)
	Set @x = Convert(Varchar(100), @Utility_Asset_XMissionStructure_Pole_CrossRemedy_Vendor_InspectionVisitId)
	raiserror(505060, 16, 1, 'UtilityAssetXMissionStructurePolesCrossRemedyVendorInspectionVisit', @x)
	return
end

-- proceed
update tb_Utility_Asset_XMissionStructure_Poles_CrossRemedy_Vendor_InspectionVisit 
set

	 [Vendor_InspVisit_XMissionStructure_PoleFK] =  @Vendor_InspVisit_XMissionStructure_PoleFK
	,[Utility_Asset_XMissionStructureFK] =  @Utility_Asset_XMissionStructureFK
	,[DiscoveredDate] =  @DiscoveredDate
	,[RemedyStatusFK] =  @RemedyStatusFK
	,[RemedyAssigned] =  @RemedyAssigned
	,[RemedyLastAction] =  @RemedyLastAction
	,[RemedyResolvedDate] =  @RemedyResolvedDate
	,[RemedyNotes] =  @RemedyNotes
	
from tb_Utility_Asset_XMissionStructure_Poles_CrossRemedy_Vendor_InspectionVisit source
where
	[source].[Utility_Asset_XMissionStructure_Pole_CrossRemedy_Vendor_InspectionVisitId] = @OriginalUtility_Asset_XMissionStructure_Pole_CrossRemedy_Vendor_InspectionVisitId


select * from v_tb_Utility_Asset_XMissionStructure_Poles_CrossRemedy_Vendor_InspectionVisit where Utility_Asset_XMissionStructure_Pole_CrossRemedy_Vendor_InspectionVisitId = @Utility_Asset_XMissionStructure_Pole_CrossRemedy_Vendor_InspectionVisitId
GO





--use PolePosition

if exists (select * from dbo.sysobjects where id = object_id(N'up_UtilityAssetXMissionStructurePolesCrossRemedyVendorInspectionVisit_Delete') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure up_UtilityAssetXMissionStructurePolesCrossRemedyVendorInspectionVisit_Delete
GO


create proc up_UtilityAssetXMissionStructurePolesCrossRemedyVendorInspectionVisit_Delete
	@Utility_Asset_XMissionStructure_Pole_CrossRemedy_Vendor_InspectionVisitId int
as
	/* 	Initial Generated Date:  Saturday, February 07, 2009 
		Purpose: Deletes an existing record, verifying exist
	*/

SET NOCOUNT ON	

-- test
declare @Result bit
execute @Result = up_UtilityAssetXMissionStructurePolesCrossRemedyVendorInspectionVisit_Test @Utility_Asset_XMissionStructure_Pole_CrossRemedy_Vendor_InspectionVisitId
if (not @Result = 1)
begin
	Declare @x varchar(100)
	Set @x = Convert(Varchar(100), @Utility_Asset_XMissionStructure_Pole_CrossRemedy_Vendor_InspectionVisitId)
	raiserror(505060, 16, 1, 'UtilityAssetXMissionStructurePolesCrossRemedyVendorInspectionVisit', @x)
	return
end

-- proceed
delete from tb_Utility_Asset_XMissionStructure_Poles_CrossRemedy_Vendor_InspectionVisit 
where [Utility_Asset_XMissionStructure_Pole_CrossRemedy_Vendor_InspectionVisitId] = @Utility_Asset_XMissionStructure_Pole_CrossRemedy_Vendor_InspectionVisitId

GO


